package com.wesley.util.excel;

import com.wesley.util.excel.annotation.ExcelColumn;
import com.wesley.util.excel.annotation.Validate;
import com.wesley.util.excel.strategy.StrategyFactory;
import com.wesley.util.string.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

/**
 *
 * excel常用操作方法
 *
 * @author wesley
 */
public class ExcelUtil {


    /**
     *
     * 判断是否是2003格式
     *
     * @param fileName
     * @return true:是 false：否
     */
    public static boolean is2003(String fileName){
        return fileName.endsWith("xls");
    }


    /**
     * 判断是否是2007格式
     * @param fileName
     * @return
     */
    public static boolean is2007(String fileName){
        return fileName.endsWith("xlsx");
    }


    /**
     *
     * 判断文件是否存在
     *
     * @param filePath
     * @return
     */
    public static boolean isFileExisted(String filePath){
        return new File(filePath).exists();
    }


    /**
     * 根据版本获取workbook
     *
     * @param  fileName
     * @return 返回workbook对象
     */
    private static Workbook getWorkbook(String fileName,InputStream in) throws IOException {
        Workbook workbook = null;
        if (is2003(fileName)){
            workbook = new HSSFWorkbook(in);
        } else{
            workbook = new XSSFWorkbook(in);
        }
        return workbook;
    }


    /**
     *
     * @param filePath 文件路径
     * @param fmt 日期格式
     * @throws IOException IO异常
     */
    public static List<?> readExcel(String filePath,String fmt,Class clazz,boolean isOverFirstLine)
            throws IOException {
        try {
            // 同时支持Excel 2003、2007
            File excelFile = new File(filePath); // 创建文件对象

            //文件流
            InputStream inputStream = new FileInputStream(excelFile);

            if (!isFileExisted(filePath)){
                return null;
            }

            Workbook workbook = getWorkbook(excelFile.getName(), inputStream);
            return readRow(workbook,fmt,clazz,isOverFirstLine);

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     *
     * 读取每一行
     *
     * @param workbook 表格对象
     * @param pattern 日期格式
     */
    private static List<?> readRow(Workbook workbook,String pattern, Class clazz,boolean isOverFirstLine)
            throws InstantiationException, IllegalAccessException {
        SimpleDateFormat fmt = new SimpleDateFormat(pattern);

        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量

        Sheet sheet = workbook.getSheetAt(0);   // 遍历第一个Sheet

        return readCellAndPackageData(sheet,clazz,isOverFirstLine,fmt);
    }


    private static List<?> readCellAndPackageData(Sheet sheet,Class clazz,boolean isOverFirstLine,SimpleDateFormat fmt)
            throws IllegalAccessException, InstantiationException {
        int count = 0;
        List<Object> objectList = new ArrayList<Object>();

        for (Row row: sheet) {
            if (isOverFirstLine && count == 0) {
                count++ ;
                continue;
            }

            //生成一个对象
            Object obj = clazz.newInstance();

            Field[] fields = clazz.getDeclaredFields();

            //反射设置值
            for (Field field: fields) {

                //方法私有情况下，可以访问
                field.setAccessible(true);

                String fieldName = field.getName();

                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnIndex = annotation.value();

                Cell cell = row.getCell(Integer.parseInt(columnIndex));
                String value = getValueByCell(cell,fmt);

                String fieldTypeName = field.getGenericType().toString();

                if (fieldTypeName.equals("class java.lang.String")) {
                    field.set(obj,value);
                } else if (fieldTypeName.equals("class java.lang.Integer") || fieldTypeName.equals("int")){
                    field.set(obj,Integer.parseInt(value));
                } else if (fieldTypeName.equals("class java.lang.Double")) {
                    field.set(obj,Double.parseDouble(value));
                } else if(fieldTypeName.equals("class java.util.Date")){
                    field.set(obj, Date.parse(value));
                }
            }

            objectList.add(obj);
        }

        return objectList;
    }


    /**
     *  读取判断单元格数据类型，返回数据
     *
     * @param cell
     * @param fmt
     * @return
     */
    private static String getValueByCell(Cell cell,SimpleDateFormat fmt){
        String cellValue = "";

        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING:     // 文本
                cellValue = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:    // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue());
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:    // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA:    // 公式
                // 得到对应单元格的公式
                //cellValue = cell.getCellFormula();
                // 得到对应单元格的字符串
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }



    /**
     * 校验
     *
     * @param tlist 对象集合
     * @return
     * @throws IllegalAccessException
     */
    public static <T> boolean checkValidate(List<T> tlist) throws IllegalAccessException {
        //todo 虽然可以基本校验了，但是不知道错误的信息

        for (T t: tlist) {
            Field[] declaredFields = t.getClass().getDeclaredFields();
            for (Field field : declaredFields) {

                //不开启，私有变量不能访问。
                field.setAccessible(true);

                Validate validate = field.getAnnotation(Validate.class);
                //获取属性的值
                Object value = field.get(t);

                StrategyFactory sf = new StrategyFactory(validate);
                return sf.checkValidate();
            }
        }
        return true;
    }

}

